﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Android_Helper.entity
{
    class SchemaUtils
    {
        /**
         *  String mysqlStr = "Database=stockdb;Data Source=192.168.1.210;User Id=root;Password=Passw0rd;pooling=false;CharSet=utf8;port=3306";
         */
        public static List<TableColumnEntity> TableColumnToEntity(string connectionString, string dbName, string tableName)
        {
            List<TableColumnEntity> tableColumnEntities = new List<TableColumnEntity>();
            string sql = "SELECT `column_name`, `data_type`, `column_comment` " +
                "FROM information_schema.columns " +
                "WHERE table_schema=@table_schema " +
                "   AND table_name=@table_name " +
                "ORDER BY ordinal_position";
            MySqlConnection mysqlconn = new MySqlConnection(connectionString);
            MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlconn);
            mySqlCommand.Parameters.Add(new MySqlParameter("table_schema", dbName));
            mySqlCommand.Parameters.Add(new MySqlParameter("table_name", tableName));
            mysqlconn.Open();
            try
            {
                MySqlDataReader reader = mySqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    TableColumnEntity columnEntity = new TableColumnEntity();
                    columnEntity.ColumnName = reader.GetString("column_name");
                    columnEntity.ColumnType = reader.GetString("data_type");
                    columnEntity.ColumnComment = reader.GetString("column_comment");
                    tableColumnEntities.Add(columnEntity);
                }
            }
            finally {
                mysqlconn.Close();
            }
            return tableColumnEntities;
        }

        public static TableColumnEntity TableToEntity(string connectionString, string dbName, string tableName)
        {
            TableColumnEntity tableEntity = new TableColumnEntity();
            string sql = "SELECT `table_name`, `table_type`, `table_comment` " +
                "FROM information_schema.tables " +
                "WHERE table_schema=@table_schema " +
                "   AND table_name=@table_name ";
            MySqlConnection mysqlconn = new MySqlConnection(connectionString);
            MySqlCommand mySqlCommand = new MySqlCommand(sql, mysqlconn);
            mySqlCommand.Parameters.Add(new MySqlParameter("table_schema", dbName));
            mySqlCommand.Parameters.Add(new MySqlParameter("table_name", tableName));
            mysqlconn.Open();
            try
            {
                MySqlDataReader reader = mySqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    tableEntity.ColumnName = reader.GetString("table_name");
                    tableEntity.ColumnType = reader.GetString("table_type");
                    tableEntity.ColumnComment = reader.GetString("table_comment");
                }
            }
            finally
            {
                mysqlconn.Close();
            }
            return tableEntity;
        }
    }
}
